﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DTaxi
    {
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "ttaxi");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string taxiNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ttaxi");
            strSql.Append(" where taxiNo=@taxiNo");
            MySqlParameter[] parameters = {
					new MySqlParameter("@taxiNo", MySqlDbType.VarChar,45)
};
            parameters[0].Value = taxiNo;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }


        public string[] GetParametersByPhoneNum(string phoneNumber)
        {
            string[] parameters = new string[3];
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append("SpeedOut,SpeedOutIsOpen,StopTimeOut,StopTimeOutIsOpen,");
            strSql.Append("FatigueDrivingTime,FatigueDrivingIsOpen ");
            strSql.Append("From ttaxi where id=(");
            strSql.Append("select TaxiID from tdriver where PhoneNumber=@PhoneNumber");
            strSql.Append(") ");            
            MySqlParameter[] parameterss = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,45)
                };
            parameterss[0].Value = phoneNumber;
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameterss);
            if(ds == null || ds.Tables[0].Rows.Count==0)
            {
                return null;
            }
            else
            {
                parameters[0] = ds.Tables[0].Rows[0]["SpeedOut"].ToString();
                parameters[1] = ds.Tables[0].Rows[0]["StopTimeOut"].ToString();
                parameters[2] = ds.Tables[0].Rows[0]["FatigueDrivingTime"].ToString();
                return parameters;
            }
            
        }

        public List<string> GetCompanyList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct Remark");
            strSql.Append(" FROM ttaxi");            
            DataSet ds= DbHelperMySQL.Query(strSql.ToString());
            if (ds==null || ds.Tables.Count==0)
            {
                return null;
            }
            List<string> list = new List<string>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                string company = dr["Remark"].ToString();
                if (company!="")
                {
                    list.Add(company);    
                }                
            }
            return list;
        }

        public bool Add(Model.TaxiInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ttaxi(");
            strSql.Append("TaxiNo,TaxiType,Remark,TaxiGroupId,");
            strSql.Append("ServerId,");
            strSql.Append("CompanyId,");
            strSql.Append("SpeedOut,");
            strSql.Append("SpeedOutIsOpen,");
            strSql.Append("StopTimeOut,");
            strSql.Append("StopTimeOutIsOpen,");
            strSql.Append("FatigueDrivingTime,");
            strSql.Append("FatigueDrivingIsOpen");
            strSql.Append(") values (");
            strSql.Append("@TaxiNo,@TaxiType,@Remark,@TaxiGroupId,");
            strSql.Append("@ServerId,");
            strSql.Append("@CompanyId,");
            strSql.Append("@SpeedOut,");
            strSql.Append("@SpeedOutIsOpen,");
            strSql.Append("@StopTimeOut,");
            strSql.Append("@StopTimeOutIsOpen,");
            strSql.Append("@FatigueDrivingTime,");
            strSql.Append("@FatigueDrivingIsOpen)");

            MySqlParameter[] parameters = {
					new MySqlParameter("@TaxiNo", MySqlDbType.VarChar,45),
					new MySqlParameter("@TaxiType", MySqlDbType.VarChar,45),
					new MySqlParameter("@Remark", MySqlDbType.Text),
                    new MySqlParameter("@TaxiGroupId", MySqlDbType.Int32),
                    new MySqlParameter("@ServerId", MySqlDbType.Int32),
                     new MySqlParameter("@CompanyId", MySqlDbType.Int32),
                    new MySqlParameter("@SpeedOut", MySqlDbType.VarChar,6),
                    new MySqlParameter("@SpeedOutIsOpen", MySqlDbType.VarChar,2),
                    new MySqlParameter("@StopTimeOut", MySqlDbType.VarChar,6),
                    new MySqlParameter("@StopTimeOutIsOpen", MySqlDbType.VarChar,2),
                    new MySqlParameter("@FatigueDrivingTime", MySqlDbType.VarChar,6),
                    new MySqlParameter("@FatigueDrivingIsOpen", MySqlDbType.VarChar,2)
                                          };
            parameters[0].Value = model.TaxiNo;
            parameters[1].Value = model.TaxiType;
            parameters[2].Value = model.Remark;
            parameters[3].Value = model.TaxiGroupId;
            parameters[4].Value = model.ServerId;
            parameters[5].Value = model.CompanyId;             
            parameters[6].Value = model.SpeedOut;
            parameters[7].Value = model.SpeedOutIsOpen;
            parameters[8].Value = model.StopOutTime;
            parameters[9].Value = model.StopOutIsOpen;
            parameters[10].Value = model.FatigueDravingTime;
            parameters[11].Value = model.FatigueDravingIsOpen;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public bool Update(Model.TaxiInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ttaxi set ");
            strSql.Append("TaxiNo=@TaxiNo,");
            strSql.Append("TaxiType=@TaxiType,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("TaxiGroupId=@TaxiGroupId,");
            strSql.Append("CompanyId=@CompanyId,");
            strSql.Append("ServerId=@ServerId,");
            strSql.Append("SpeedOut=@SpeedOut,");
            strSql.Append("SpeedOutIsOpen=@SpeedOutIsOpen,");
            strSql.Append("StopTimeOut=@StopTimeOut,");
            strSql.Append("StopTimeOutIsOpen=@StopTimeOutIsOpen,");
            strSql.Append("FatigueDrivingTime=@FatigueDrivingTime,");
            strSql.Append("FatigueDrivingIsOpen=@FatigueDrivingIsOpen");

            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@TaxiNo", MySqlDbType.VarChar,45),
					new MySqlParameter("@TaxiType", MySqlDbType.VarChar,45),
					new MySqlParameter("@Remark", MySqlDbType.Text),
                    new MySqlParameter("@TaxiGroupId", MySqlDbType.Int32),
                       new MySqlParameter("@CompanyId", MySqlDbType.Int32),
                    new MySqlParameter("@ServerId", MySqlDbType.Int32),
                    new MySqlParameter("@SpeedOut", MySqlDbType.VarChar,6),
                    new MySqlParameter("@SpeedOutIsOpen", MySqlDbType.VarChar,2),
                    new MySqlParameter("@StopTimeOut", MySqlDbType.VarChar,6),
                    new MySqlParameter("@StopTimeOutIsOpen", MySqlDbType.VarChar,2),
                    new MySqlParameter("@FatigueDrivingTime", MySqlDbType.VarChar,6),
                    new MySqlParameter("@FatigueDrivingIsOpen", MySqlDbType.VarChar,2),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.TaxiNo;
            parameters[1].Value = model.TaxiType;
            parameters[2].Value = model.Remark;
            parameters[3].Value = model.TaxiGroupId;

            parameters[4].Value = model.CompanyId;
            parameters[5].Value = model.ServerId;

            parameters[6].Value = model.SpeedOut;
            parameters[7].Value = model.SpeedOutIsOpen;
            parameters[8].Value = model.StopOutTime;
            parameters[9].Value = model.StopOutIsOpen;
            parameters[10].Value = model.FatigueDravingTime;
            parameters[11].Value = model.FatigueDravingIsOpen;

            parameters[12].Value = model.Id;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ttaxi ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ttaxi ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
       
        public Model.ServerConfigInfo GetServerConfigByTaxiId(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ServerId,tServerconfig.ServerName, ");
            strSql.Append("tServerconfig.LongitudeOffset,tServerconfig.LatitudeOffset,");
            strSql.Append("tServerconfig.DNSURL from ttaxi left join tServerconfig on");
            strSql.Append("  ServerId = tServerconfig.id where ttaxi.id=");
            strSql.Append(id);
            Model.ServerConfigInfo model = new Model.ServerConfigInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if(ds!= null && ds.Tables.Count !=0 && ds.Tables[0].Rows.Count!=0)
            {
                if (ds.Tables[0].Rows[0]["ServerName"] == null) return null;

                if (ds.Tables[0].Rows[0]["ServerId"] != null && ds.Tables[0].Rows[0]["ServerId"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["ServerId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ServerName"] != null && ds.Tables[0].Rows[0]["ServerName"].ToString() != "")
                {
                    model.ServerName = ds.Tables[0].Rows[0]["ServerName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LongitudeOffset"] != null && ds.Tables[0].Rows[0]["LongitudeOffset"].ToString() != "")
                {
                    model.LongitudeOffset = ds.Tables[0].Rows[0]["LongitudeOffset"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LatitudeOffset"] != null && ds.Tables[0].Rows[0]["LatitudeOffset"].ToString() != "")
                {
                    model.LatitudeOffset = ds.Tables[0].Rows[0]["LatitudeOffset"].ToString();
                }
                if (ds.Tables[0].Rows[0]["DNSURL"] != null && ds.Tables[0].Rows[0]["DNSURL"].ToString() != "")
                {
                    model.DNSURL = ds.Tables[0].Rows[0]["DNSURL"].ToString();
                }
                return model;  
            }                                  
            return null;           
        }

        public DataSet GetList(string strWhere)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select id,TaxiNo,TaxiType,Remark,TaxiGroupId,TaxiGroup,ServerId ");
            //strSql.Append(" FROM vtaxigroup ");
            //if (strWhere.Trim() != "")
            //{
            //    strSql.Append(" where " + strWhere);
            //}
            //return DbHelperMySQL.Query(strSql.ToString());

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.id,t.TaxiNo,t.TaxiType,t.Remark,");
            strSql.Append("t.TaxiGroupid,tg.Taxigroup,t.ServerId,");
            strSql.Append("ts.ServerName,t.CompanyId,");
            strSql.Append("tc.CompanyName from ttaxi t ");
            strSql.Append("left join ttaxigroup tg on t.TaxiGroupId = tg.Id ");
            strSql.Append("left join tserverconfig ts on t.ServerId = ts.id ");
            strSql.Append("left join tCompany tc on t.CompanyId= tc.Id ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());

        }

        public DataSet GetList(string parameter, string parameterVaule)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select id,TaxiNo,TaxiType,Remark,TaxiGroupId,TaxiGroup,ServerId ");
            //strSql.Append(" FROM vtaxigroup ");
            //strSql.Append(" where ");
            //strSql.Append(parameter);
            //strSql.Append(" like @A");
            //MySqlParameter[] parameters = {
            //                                  new MySqlParameter("@A", MySqlDbType.Text)
            //                              };
            //parameters[0].Value = "%" + parameterVaule + "%";
            //return DbHelperMySQL.Query(strSql.ToString(), parameters); 

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.id,t.TaxiNo,t.TaxiType,t.Remark,");
            strSql.Append("t.TaxiGroupid,tg.Taxigroup,t.ServerId,");
            strSql.Append("ts.ServerName,t.CompanyId,");
            strSql.Append("tc.CompanyName from ttaxi t ");
            strSql.Append("left join ttaxigroup tg on t.TaxiGroupId = tg.Id ");
            strSql.Append("left join tserverconfig ts on t.ServerId = ts.id ");
            strSql.Append("left join tCompany tc on t.CompanyId= tc.Id ");
           
            strSql.Append(" where ");
            strSql.Append(parameter);
            strSql.Append(" like @A");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@A", MySqlDbType.Text)
                                          };
            parameters[0].Value = "%" + parameterVaule + "%";
            return DbHelperMySQL.Query(strSql.ToString(), parameters); 


        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.TaxiInfo GetModel(int id)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select id,TaxiNo,TaxiType,Remark,TaxiGroupId,");            
            //strSql.Append("SpeedOut,SpeedOutIsOpen,StopTimeOut,StopTimeOutIsOpen,");
            //strSql.Append("FatigueDrivingTime,FatigueDrivingIsOpen");
            //strSql.Append(" from ttaxi where id=@id");
            //MySqlParameter[] parameters = {
            //        new MySqlParameter("@id", MySqlDbType.Int32)};
            //parameters[0].Value = id;


            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT t.id,t.TaxiNo,t.TaxiType,t.Remark,");
            strSql.Append("t.TaxiGroupId,tg.TaxiGroup,t.ServerId,");
            strSql.Append("ts.ServerName,t.CompanyId,");
            strSql.Append("t.SpeedOut,t.SpeedOutIsOpen,");
            strSql.Append("t.StopTimeOut,t.StopTimeOutIsOpen,");
            strSql.Append("t.FatigueDrivingTime,t.FatigueDrivingIsOpen,");  
            strSql.Append("tc.CompanyName from ttaxi t ");
            strSql.Append("left join ttaxigroup tg on t.TaxiGroupId = tg.Id ");
            strSql.Append("left join tserverconfig ts on t.ServerId = ts.id ");
            strSql.Append("left join tCompany tc on t.CompanyId= tc.Id ");
            strSql.Append(" where t.id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)};
            parameters[0].Value = id;



            Model.TaxiInfo model = new Model.TaxiInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TaxiNo"] != null && ds.Tables[0].Rows[0]["TaxiNo"].ToString() != "")
                {
                    model.TaxiNo = ds.Tables[0].Rows[0]["TaxiNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TaxiType"] != null && ds.Tables[0].Rows[0]["TaxiType"].ToString() != "")
                {
                    model.TaxiType = ds.Tables[0].Rows[0]["TaxiType"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Remark"] != null && ds.Tables[0].Rows[0]["Remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TaxiGroupId"] != null && ds.Tables[0].Rows[0]["TaxiGroupId"].ToString() != "")
                {
                    model.TaxiGroupId = int.Parse(ds.Tables[0].Rows[0]["TaxiGroupId"].ToString());
                }

                if (ds.Tables[0].Rows[0]["CompanyId"] != null && ds.Tables[0].Rows[0]["CompanyId"].ToString() != "")
                {
                    model.CompanyId = int.Parse(ds.Tables[0].Rows[0]["CompanyId"].ToString());
                }

                if (ds.Tables[0].Rows[0]["CompanyName"] != null && ds.Tables[0].Rows[0]["CompanyName"].ToString() != "")
                {
                    model.CompanyName = ds.Tables[0].Rows[0]["CompanyName"].ToString();
                }



                if (ds.Tables[0].Rows[0]["SpeedOut"] != null && ds.Tables[0].Rows[0]["SpeedOut"].ToString() != "")
                {
                    model.SpeedOut = int.Parse(ds.Tables[0].Rows[0]["SpeedOut"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpeedOutIsOpen"] != null && ds.Tables[0].Rows[0]["SpeedOutIsOpen"].ToString() != "")
                {
                    model.SpeedOutIsOpen = int.Parse(ds.Tables[0].Rows[0]["SpeedOutIsOpen"].ToString());
                }

                if (ds.Tables[0].Rows[0]["StopTimeOut"] != null && ds.Tables[0].Rows[0]["StopTimeOut"].ToString() != "")
                {
                    model.StopOutTime = int.Parse(ds.Tables[0].Rows[0]["StopTimeOut"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StopTimeOutIsOpen"] != null && ds.Tables[0].Rows[0]["StopTimeOutIsOpen"].ToString() != "")
                {
                    model.StopOutIsOpen = int.Parse(ds.Tables[0].Rows[0]["StopTimeOutIsOpen"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FatigueDrivingTime"] != null && ds.Tables[0].Rows[0]["FatigueDrivingTime"].ToString() != "")
                {
                    model.FatigueDravingTime = int.Parse(ds.Tables[0].Rows[0]["FatigueDrivingTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FatigueDrivingIsOpen"] != null && ds.Tables[0].Rows[0]["FatigueDrivingIsOpen"].ToString() != "")
                {
                    model.FatigueDravingIsOpen = int.Parse(ds.Tables[0].Rows[0]["FatigueDrivingIsOpen"].ToString());
                }
              
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM ttaxi ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from ttaxi T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public bool UpdateServerIdByTaxiId(int taxiId,int serverId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ttaxi set ");
            strSql.Append("ServerId=@ServerId");           
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {				 
                    new MySqlParameter("@ServerId", MySqlDbType.Int32),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = serverId;
            parameters[1].Value = taxiId;           
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
